package com.cho.core.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.cho.core.db.SQLiteConnection;
import com.cho.core.exception.TableCreateException;

public class TableCreate {
	public static void runTableCreate() throws Exception{
		
		String createProjectInfo = ""
				+ "CREATE TABLE IF NOT EXISTS PROJECT_INFO "
				+ "  ( "
				+ "     PROJECT_NAME TEXT, "
				+ "     PROJECT_INFO TEXT, "
				+ "     PROJECT_PATH TEXT, "
				+ "     REG_DATE     TEXT, "
				+ "     PRIMARY KEY (PROJECT_NAME, REG_DATE) "
				+ "  ) ";	
		
		String createFileList = ""
				+ "CREATE TABLE IF NOT EXISTS FILE_LIST "
				+ "  ( "
				+ "     PROJECT_NAME 		TEXT , "
				+ "     FILE_SEQ     		TEXT, "
				+ "     FILE_NAME    		TEXT, "
				+ "     FILE_PATH    		TEXT, "
				+ "     FILE_TOTAL_LINE    	TEXT, "
				+ "     PRIMARY KEY (PROJECT_NAME, FILE_SEQ) "
				+ "  ) ";
		
		String createTableList = ""
				+ "CREATE TABLE IF NOT EXISTS TABLE_LIST "
				+ "  ( "
				+ "     PROJECT_NAME TEXT, "
				+ "     FILE_SEQ     TEXT, "
				+ "     TABLE_SEQ    INTEGER, "
				+ "     TABLE_NAME   TEXT, "
				+ "     CRUD_TYPE    TEXT, "
				+ "     DB_LINK      TEXT, "
				+ "     SOURCE_LINE  TEXT, "
				+ "     PRIMARY KEY (PROJECT_NAME, FILE_SEQ, TABLE_SEQ) "				
				+ "  ) ";

		String createColumnList = ""
				+ "CREATE TABLE IF NOT EXISTS COLUMN_LIST "
				+ "  ( "
				+ "     PROJECT_NAME TEXT, "
				+ "     FILE_SEQ     INTEGER, "
				+ "     COLUMN_SEQ   INTEGER, "
				+ "     COLUMN_NAME  TEXT, "
				+ "     COLUMN_TYPE  TEXT, "
				+ "     SOURCE_LINE  TEXT, "
				+ "     PRIMARY KEY (PROJECT_NAME, FILE_SEQ, COLUMN_SEQ) "				
				+ "  ) ";		
		
		String createDbTableList = ""
				+ "CREATE TABLE IF NOT EXISTS DB_TABLE_INFO "
				+ "  ( "
				+ "     PROJECT_NAME TEXT, "
				+ "     TABLE_NAME  TEXT, "
				+ "     COMMENTS  TEXT, "
				+ "     PRIMARY KEY (PROJECT_NAME, TABLE_NAME) "				
				+ "  ) ";		
		
		executeStatement(createProjectInfo);
		executeStatement(createFileList);
		executeStatement(createTableList);
		executeStatement(createColumnList);
		executeStatement(createDbTableList);
	}
	
	public static void dropTable() throws Exception{
		
		String createProjectInfo = ""
				+ "DROP TABLE PROJECT_INFO ";
		
		String createFileList = ""
				+ "DROP TABLE FILE_LIST ";
		
		String createTableList = ""
				+ "DROP TABLE TABLE_LIST ";
		
		String createColumnList = ""
				+ "DROP TABLE COLUMN_LIST ";	
		
		String createDbTableList = ""
				+ "DROP TABLE DB_TABLE_INFO ";		
		
		executeStatement(createProjectInfo);
		executeStatement(createFileList);
		executeStatement(createTableList);
		executeStatement(createColumnList);
		executeStatement(createDbTableList);
	}
	
	private static void executeStatement(String sql) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		conn = SQLiteConnection.getConnection();
		
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("테이블 생성 실패");
			throw new TableCreateException(sql);
		} finally {
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
	}
}
